-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `office_management_system`
--

-- --------------------------------------------------------

--
-- Table structure for table `ci_sessions`
--

CREATE TABLE IF NOT EXISTS `ci_sessions` (
  `id` varchar(128) NOT NULL,
  `ip_address` varchar(45) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `data` blob NOT NULL,
  PRIMARY KEY (`id`,`ip_address`),
  KEY `ci_sessions_timestamp` (`timestamp`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `oms_admin_memo`
--

CREATE TABLE IF NOT EXISTS `oms_admin_memo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `admin_id` int(11) NOT NULL,
  `important` int(1) NOT NULL DEFAULT '0',
  `title` varchar(100) NOT NULL,
  `description` varchar(5000) NOT NULL,
  `status` int(1) NOT NULL DEFAULT '1' COMMENT '0=delete\r\n1=publish',
  `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `oms_application`
--

CREATE TABLE IF NOT EXISTS `oms_application` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `staff_id` int(11) NOT NULL,
  `application_type_id` int(3) NOT NULL DEFAULT '1',
  `reason` varchar(200) NOT NULL,
  `attachment` varchar(100) DEFAULT NULL,
  `day` int(11) NOT NULL,
  `application_date` date NOT NULL,
  `apply_daterange` varchar(30) NOT NULL,
  `status` int(2) NOT NULL DEFAULT '1' COMMENT '1=pending\r\n2=approve\r\n3=reject\r\n4=cancel',
  `approved_by` int(11) NOT NULL DEFAULT '0' COMMENT 'admin id\r\n0=new request',
  `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `oms_application_admin_message`
--

CREATE TABLE IF NOT EXISTS `oms_application_admin_message` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `application_id` int(11) NOT NULL,
  `admin_id` int(11) NOT NULL,
  `title` varchar(100) NOT NULL,
  `description` varchar(8000) NOT NULL,
  `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `oms_application_group`
--

CREATE TABLE IF NOT EXISTS `oms_application_group` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `staff_id` int(11) NOT NULL,
  `application_id` int(11) NOT NULL,
  `application_message_id` int(11) NOT NULL,
  `status` int(2) NOT NULL,
  `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `oms_application_staff_message`
--

CREATE TABLE IF NOT EXISTS `oms_application_staff_message` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `application_id` int(11) NOT NULL,
  `staff_id` int(11) NOT NULL,
  `title` varchar(100) NOT NULL,
  `description` varchar(8000) DEFAULT NULL,
  `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `oms_application_type`
--

CREATE TABLE IF NOT EXISTS `oms_application_type` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL DEFAULT '1',
  `status` int(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `oms_claim`
--

CREATE TABLE IF NOT EXISTS `oms_claim` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `staff_id` int(11) NOT NULL,
  `claim_type_id` int(3) NOT NULL DEFAULT '1',
  `remark` varchar(200) NOT NULL,
  `attachment` varchar(100) DEFAULT NULL,
  `day` int(11) NOT NULL,
  `claim_date` date NOT NULL,
  `amount` double(16,2) NOT NULL,
  `status` int(2) NOT NULL DEFAULT '1' COMMENT '1=pending\r\n2=approve\r\n3=reject\r\n4=cancel',
  `approved_by` int(11) NOT NULL DEFAULT '0' COMMENT 'admin id\r\n0=new request',
  `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `oms_claim_admin_message`
--

CREATE TABLE IF NOT EXISTS `oms_claim_admin_message` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `claim_id` int(11) NOT NULL,
  `admin_id` int(11) NOT NULL,
  `title` varchar(100) NOT NULL,
  `description` varchar(8000) NOT NULL,
  `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `oms_claim_group`
--

CREATE TABLE IF NOT EXISTS `oms_claim_group` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `staff_id` int(11) NOT NULL,
  `claim_id` int(11) NOT NULL,
  `claim_message_id` int(11) NOT NULL,
  `status` int(2) NOT NULL,
  `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `oms_claim_staff_message`
--

CREATE TABLE IF NOT EXISTS `oms_claim_staff_message` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `claim_id` int(11) NOT NULL,
  `staff_id` int(11) NOT NULL,
  `title` varchar(100) NOT NULL,
  `description` varchar(8000) DEFAULT NULL,
  `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `oms_claim_type`
--

CREATE TABLE IF NOT EXISTS `oms_claim_type` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `status` int(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `oms_department`
--

CREATE TABLE IF NOT EXISTS `oms_department` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `status` int(2) NOT NULL DEFAULT '1',
  `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `oms_department_memo`
--

CREATE TABLE IF NOT EXISTS `oms_department_memo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `staff_id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `important` int(1) NOT NULL DEFAULT '0',
  `title` varchar(100) NOT NULL,
  `description` varchar(5000) NOT NULL,
  `status` int(1) NOT NULL DEFAULT '1' COMMENT '0=delete\r\n1=publish',
  `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `oms_staff_payroll`
--

CREATE TABLE IF NOT EXISTS `oms_staff_payroll` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `admin_id` int(11) NOT NULL,
  `staff_id` int(11) NOT NULL,
  `basic_salary` double NOT NULL,
  `allowance` double NOT NULL,
  `epf_employer_contribute` double(16,2) NOT NULL,
  `epf_employee_contribute` double(16,2) NOT NULL,
  `socso_employer_contribute` double(16,2) NOT NULL,
  `socso_employee_contribute` double(16,2) NOT NULL,
  `eis_employer_contribute` double(16,2) NOT NULL,
  `eis_employee_contribute` double(16,2) NOT NULL,
  `total_epf_contribute` double(16,2) NOT NULL,
  `total_socso_contribute` double(16,2) NOT NULL,
  `payroll_day` varchar(7) NOT NULL,
  `status` int(2) NOT NULL DEFAULT '1',
  `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `oms_staff_salary_wallet`
--

CREATE TABLE IF NOT EXISTS `oms_staff_salary_wallet` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `staff_id` int(11) NOT NULL,
  `start_salary` double(16,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `oms_sys_groups`
--

CREATE TABLE IF NOT EXISTS `oms_sys_groups` (
  `id` mediumint(8) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `description` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `oms_sys_groups`
--

INSERT INTO `oms_sys_groups` (`id`, `name`, `description`) VALUES
(1, 'admin', 'Administrator'),
(2, 'members', 'General User');

-- --------------------------------------------------------

--
-- Table structure for table `oms_sys_login_attempts`
--

CREATE TABLE IF NOT EXISTS `oms_sys_login_attempts` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ip_address` varchar(45) NOT NULL,
  `login` varchar(100) NOT NULL,
  `time` int(11) UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `oms_sys_users_groups`
--

CREATE TABLE IF NOT EXISTS `oms_sys_users_groups` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` int(11) UNSIGNED NOT NULL,
  `group_id` mediumint(8) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uc_users_groups` (`user_id`,`group_id`),
  KEY `fk_users_groups_users1_idx` (`user_id`),
  KEY `fk_users_groups_groups1_idx` (`group_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `oms_sys_users_groups`
--

INSERT INTO `oms_sys_users_groups` (`id`, `user_id`, `group_id`) VALUES
(1, 1, 1),
(2, 1, 2),
(5, 2, 2),
(6, 3, 2);

-- --------------------------------------------------------

--
-- Table structure for table `oms_sys_user_detail`
--

CREATE TABLE IF NOT EXISTS `oms_sys_user_detail` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `epf_no` varchar(20) NOT NULL,
  `basic_salary` double(16,2) NOT NULL,
  `full_name` varchar(200) NOT NULL,
  `NRIC` varchar(12) NOT NULL,
  `email_address` varchar(100) NOT NULL,
  `phone` varchar(12) NOT NULL,
  `address` varchar(200) NOT NULL,
  `annual_leave` int(11) NOT NULL DEFAULT '7',
  `department_id` int(11) NOT NULL DEFAULT '0',
  `start_join` date NOT NULL,
  `end_join` date DEFAULT NULL,
  `DOB` date DEFAULT NULL,
  `status` int(2) NOT NULL DEFAULT '1',
  `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `oms_sys_user_detail`
--

INSERT INTO `oms_sys_user_detail` (`id`, `user_id`, `epf_no`, `basic_salary`, `full_name`, `NRIC`, `email_address`, `phone`, `address`, `annual_leave`, `department_id`, `start_join`, `end_join`, `DOB`, `status`, `date_added`, `last_update`) VALUES
(1, 2, '111111111111', 1500.00, 'staff a', '111111111111', 'staffa@gmail.com', '111111111111', 'staff a address', 7, 2, '2022-01-17', NULL, NULL, 1, '2022-01-17 01:35:19', '2022-01-17 04:02:49'),
(2, 3, '222222222222', 2000.00, 'staff b', '222222222222', 'staffb@gmail.com', '222222222222', 'staff b address', 7, 2, '2022-01-18', NULL, NULL, 1, '2022-01-18 02:56:41', '2022-01-18 03:01:58');

--
-- Constraints for dumped tables
--

--
-- Constraints for table `oms_sys_users_groups`
--
ALTER TABLE `oms_sys_users_groups`
  ADD CONSTRAINT `fk_users_groups_groups1` FOREIGN KEY (`group_id`) REFERENCES `oms_sys_groups` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_users_groups_users1` FOREIGN KEY (`user_id`) REFERENCES `oms_sys_user` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION;
COMMIT;

